﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using LantinxShare.LBS;
using LantinxShare.LBS.Location;
using System.Data;
using LantixLBSServer.Web.DbAccess.Helper;
using LantixLBSServer.Web.Common.Helper;

namespace LantixLBSServer.Web.DbAccess
{
    public class LocationManager : IManager
    {
        public List<GUserLocation> GetLocation()
        {
            List<GUserLocation> list = new List<GUserLocation>();
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                IDataReader reader = db.ExecuteDataReader(@"select t2.id,t2.UserName,t2.Password,t2.UserAlias,t2.RoleId,t2.Phone,
                                                    t3.id,t3.roleId,t3.roleName,
                                                    t1.id,t1.UserId,t1.Title,t1.Longitude,t1.Latitude,t1.Speed,t1.Altitude,t1.GTimeStamp 
                                                    from t_location t1,t_user t2,t_role t3 
                                                    where t1.UserId=t2.id and t2.roleId=t3.roleid", false);
                while (reader.Read())
                {
                    GUserLocation role = new GUserLocation()
                    {

                        User = new GUser()
                        {
                            Id = reader.GetInt32(0),
                            UserName = reader.GetString(1),
                            Password = reader.GetString(2),
                            UserAlias = reader.GetString(3),
                            Phone = reader.GetString(5),
                            Role = new GRole()
                            {
                                Id = reader.GetInt32(6),
                                RoleId = reader.GetInt32(7),
                                RoleName = reader.GetString(8),
                            }
                        },

                        Id = reader.GetInt32(9),
                        Location = new GLocation()
                        {
                            Title = reader.GetString(11),
                            Location = new GPoint(reader.GetDouble(12), reader.GetDouble(13)),
                            Speed = reader.GetDouble(14),
                            Altitude = reader.GetDouble(15),
                            GTimeStamp = CDateStampEx.ConvertToDateTime(reader.GetInt64(16))
                        }
                    };
                    list.Add(role);
                }

            }, () => db.CloseConnection());
            return list;
        }

        public List<GUserLocation> GetLocationByUserId(int userId)
        {
            List<GUserLocation> list = new List<GUserLocation>();
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@UserId");
                db.SetParameterValue("@UserId", userId);
                IDataReader reader = db.ExecuteDataReader(@"select t2.id,t2.UserName,t2.Password,t2.UserAlias,t2.RoleId,t2.Phone,
                                                    t3.id,t3.roleId,t3.roleName,
                                                    t1.id,t1.UserId,t1.Title,t1.Longitude,t1.Latitude,t1.Speed,t1.Altitude,t1.GTimeStamp 
                                                    from t_location t1,t_user t2,t_role t3 
                                                    where t1.UserId=t2.id and t2.roleId=t3.roleid and t2.id = @UserId", false);
                while (reader.Read())
                {
                    GUserLocation role = new GUserLocation()
                    {

                        User = new GUser()
                        {
                            Id = reader.GetInt32(0),
                            UserName = reader.GetString(1),
                            Password = reader.GetString(2),
                            UserAlias = reader.GetString(3),
                            Phone = reader.GetString(5),
                            Role = new GRole()
                            {
                                Id = reader.GetInt32(6),
                                RoleId = reader.GetInt32(7),
                                RoleName = reader.GetString(8),
                            }
                        },

                        Id = reader.GetInt32(9),
                        Location = new GLocation()
                        {
                            Title = reader.GetString(11),
                            Location = new GPoint(reader.GetDouble(12), reader.GetDouble(13)),
                            Speed = reader.GetDouble(14),
                            Altitude = reader.GetDouble(15),
                            GTimeStamp = CDateStampEx.ConvertToDateTime(reader.GetInt64(16))
                        }
                    };
                    list.Add(role);
                }

            }, () => db.CloseConnection());
            return list;
        }

        public List<GUserLocation> GetLocationByUserIdDate(int userId, DateTime date)
        {
            List<GUserLocation> list = new List<GUserLocation>();
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@UserId");
                db.AddParameter("@StartTime");
                db.AddParameter("@EndTime");

                db.SetParameterValue("@UserId", userId);
                db.SetParameterValue("@StartTime", date.ToString("yyyyMMdd000000"));
                db.SetParameterValue("@EndTime", date.AddDays(1).ToString("yyyyMMdd000000"));

                IDataReader reader = db.ExecuteDataReader(@"select t2.id,t2.UserName,t2.Password,t2.UserAlias,t2.RoleId,t2.Phone,
                                                    t3.id,t3.roleId,t3.roleName,
                                                    t1.id,t1.UserId,t1.Title,t1.Longitude,t1.Latitude,t1.Speed,t1.Altitude,t1.GTimeStamp 
                                                    from t_location t1,t_user t2,t_role t3 
                                                    where t1.UserId=t2.id and t2.roleId=t3.roleid and t2.id = @UserId and (t1.GTimeStamp>=@StartTime and t1.GTimeStamp <=@EndTime)", false);
                while (reader.Read())
                {
                    GUserLocation role = new GUserLocation()
                    {

                        User = new GUser()
                        {
                            Id = reader.GetInt32(0),
                            UserName = reader.GetString(1),
                            Password = reader.GetString(2),
                            UserAlias = reader.GetString(3),
                            Phone = reader.GetString(5),
                            Role = new GRole()
                            {
                                Id = reader.GetInt32(6),
                                RoleId = reader.GetInt32(7),
                                RoleName = reader.GetString(8),
                            }
                        },

                        Id = reader.GetInt32(9),
                        Location = new GLocation()
                        {
                            Title = reader.GetString(11),
                            Location = new GPoint(reader.GetDouble(12), reader.GetDouble(13)),
                            Speed = reader.GetDouble(14),
                            Altitude = reader.GetDouble(15),
                            GTimeStamp = CDateStampEx.ConvertToDateTime(reader.GetInt64(16))
                        }
                    };
                    list.Add(role);
                }

            }, () => db.CloseConnection());
            return list;
        }

        public bool InsertLocal(GUser user, GLocation location)
        {
            bool result = false;
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@UserId");
                db.SetParameterValue("@UserId", user.Id);
                db.AddParameter("@Title");
                db.SetParameterValue("@Title", location.Title);
                db.AddParameter("@Longitude");
                db.SetParameterValue("@Longitude", location.Location.Longitude);
                db.AddParameter("@Latitude");
                db.SetParameterValue("@Latitude", location.Location.Latitude);
                db.AddParameter("@Speed");
                db.SetParameterValue("@Speed", location.Speed);
                db.AddParameter("@Altitude");
                db.SetParameterValue("@Altitude", location.Altitude);
                db.AddParameter("@GTimeStamp");
                db.SetParameterValue("@GTimeStamp", location.GTimeStamp.ToString("yyyyMMddHHmm00"));
                if (db.ExecuteNonQuery(@"insert into t_location(UserId,Title,Longitude,Latitude,Speed,Altitude,GTimeStamp) 
                        values(@UserId,@Title,@Longitude,@Latitude,@Speed,@Altitude,@GTimeStamp)", false, true) > 0)
                {
                    result = true;
                }
            });
            return result;
        }

        public bool DeleteLocalById(int id)
        {
            bool result = false;
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@UserId");
                db.SetParameterValue("@UserId", id);
                if (db.ExecuteNonQuery(@"delete from t_location where UserId=@UserId", false, true) > 0)
                {
                    result = true;
                }

            }, () => db.CloseConnection());
            return result;
        }
    }
}